﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using FOModels.Customer;
using FOModels.Card;

namespace DataAccess.Card
{
    public class CardDAL
    {
        private SQLDataAccess db;
        public CardDAL()
        {
            db = new SQLDataAccess();
        }
        public DataTable getcontractbyid(int cardapplyid)
        {
            return db.exeSelect("select ca.CardApplyID,c.CardID,ca.CustomerName,ca.CustomerEmail,ca.CustomerPhone,ca.CustomerIncome,ca.CustomerDOB,ca.AppDate,c.CardImage,c.CardName,c.Cost,c.Interest,ca.StatusID,ta.StatusName,co.CompanyLogo,cl.Name from CardApply ca join [Card] c on ca.CardID = c.CardID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.CardApplyID = " + cardapplyid + " and cl.LangID = 1");
        }
        public DataTable getcontractbycateid(int cateid, int statusid)
        {
            return db.exeSelect("select ca.CardApplyID,c.CardID,ca.CustomerName,ca.CustomerEmail,ca.CustomerPhone,ca.AppDate,c.CardName,c.CardImage,ta.StatusName from CardApply ca join [Card] c on ca.CardID = c.CardID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID where co.Active = 1 and c.CardCategoryID = " + cateid + " and ca.StatusID = " + statusid + " ");
        }
        public DataTable getcontractall(int cateid)
        {
            return db.exeSelect("select ca.CardApplyID,c.CardID,ca.CustomerName,ca.CustomerEmail,ca.CustomerPhone,ca.AppDate,c.CardName,c.CardImage,ta.StatusName from CardApply ca join [Card] c on ca.CardID = c.CardID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID where co.Active = 1 and c.CardCategoryID = " + cateid + "");
        }
        public DataTable getCardProperties(int langid)
        {
            return db.exeSelect("select c.CardPropertiesID,cl.CardPropertiesName from CardProperties c join CardPropertiesLang cl on c.CardPropertiesID = cl.CardPropertiesID where cl.LangID = " + langid + "");
        }
        public DataTable getCreditCardList(int CardCategoryID, int langid)
        {
            return db.exeSelect("select ca.CardID,cl.CardName,co.CompanyID,cl.BenefitContent,cl.FurtureContent,ca.CardImage,ca.CardCategoryID,ca.CardPropertiesID,cl.Description,ca.Cost,ca.Interest,cl.MoreBenefit,cl.MoreFee,cl.MoreInfo,ca.Display,co.CompanyLogo,cla.Name from Card ca join CardLang cl on ca.CardID = cl.CardID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cla on co.CompanyID = cla.CompanyID where co.Active = 1 and ca.CardCategoryID = " + CardCategoryID + " and Display=1 and cl.LangID = " + langid + " and cla.LangID = " + langid + " order by ca.Sort");
        }
        public DataTable getTop1Card()
        {
            return db.exeSelect("select top 1 * from Card order by CardID desc");
        }
        public DataTable getallcard(int CardCategoryID, int langid)
        {
            return db.exeSelect("select * from Card ca join CardLang cl on ca.CardID = cl.CardID join Language la on cl.LangID = la.ID join Company co on ca.CompanyID = co.CompanyID where LangID = " + langid + " and ca.CardCategoryID =" + CardCategoryID + "");
        }
        public DataTable getCreditCardByProvider(int CardCategoryID, int CompanyID, int langid)
        {
            return db.exeSelect("select ca.CardID,cl.CardName,co.CompanyID,cl.BenefitContent,cl.FurtureContent,ca.CardImage,ca.CardCategoryID,ca.CardPropertiesID,cl.Description,ca.Cost,ca.Interest,cl.MoreBenefit,cl.MoreFee,cl.MoreInfo,ca.Display,co.CompanyLogo,cla.Name from Card ca join CardLang cl on ca.CardID = cl.CardID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cla on co.CompanyID = cla.CompanyID where  co.Active = 1 and ca.CardCategoryID = " + CardCategoryID + " and Display=1 and cl.LangID = " + langid + " and cla.LangID = " + langid + " and ca.CompanyID= " + CompanyID + "order by ca.Sort");
        }
        public DataTable getcreditcardrelati(int CardCategoryID, int CompanyID, int CardID, int langid)
        {
            return db.exeSelect("select ca.CardID,cl.CardName,co.CompanyID,cl.BenefitContent,cl.FurtureContent,ca.CardImage,ca.CardCategoryID,ca.CardPropertiesID,cl.Description,ca.Cost,ca.Interest,cl.MoreBenefit,cl.MoreFee,cl.MoreInfo,ca.Display,co.CompanyLogo,cla.Name from Card ca join CardLang cl on ca.CardID = cl.CardID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cla on co.CompanyID = cla.CompanyID where  co.Active = 1 and ca.CardCategoryID = " + CardCategoryID + "and ca.CompanyID =" + CompanyID + " and ca.CardID !=" + CardID + " and ca.Display = 1 and cl.LangID = " + langid + " and cla.LangID = " + langid + "order by ca.Sort");
        }
        public DataTable getCreditCardListByProperties(int CardCategoryID, int CardPropertiesID, int langid)
        {
            return db.exeSelect("select ca.CardID,cl.CardName,co.CompanyID,cl.BenefitContent,cl.FurtureContent,ca.CardImage,ca.CardCategoryID,ca.CardPropertiesID,cl.Description,ca.Cost,ca.Interest,cl.MoreBenefit,cl.MoreFee,cl.MoreInfo,ca.Display,co.CompanyLogo,cla.Name from Card ca join CardLang cl on ca.CardID = cl.CardID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cla on co.CompanyID = cla.CompanyID where  co.Active = 1 and ca.CardCategoryID = " + CardCategoryID + " and Display=1 and cl.LangID = " + langid + " and cla.LangID = " + langid + " and ca.CardPropertiesID = " + CardPropertiesID + "order by ca.Sort");
        }
        public DataTable getcardbycom_pro(int CardCategoryID, int CardPropertiesID, int CompanyID, int langid)
        {
            return db.exeSelect("select ca.CardID,cl.CardName,co.CompanyID,cl.BenefitContent,cl.FurtureContent,ca.CardImage,ca.CardCategoryID,ca.CardPropertiesID,cl.Description,ca.Cost,ca.Interest,cl.MoreBenefit,cl.MoreFee,cl.MoreInfo,ca.Display,co.CompanyLogo,cla.Name from Card ca join CardLang cl on ca.CardID = cl.CardID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cla on co.CompanyID = cla.CompanyID where  co.Active = 1 and ca.CardCategoryID = " + CardCategoryID + " and Display=1 and cl.LangID = " + langid + " and cla.LangID = " + langid + " and ca.CardPropertiesID = " + CardPropertiesID + " and ca.CompanyID = " + CompanyID + "order by ca.Sort");
        }
        public DataTable getCardPropertiesATM(int langid)
        {
            return db.exeSelect("select top 3 * from CardProperties c join CardPropertiesLang cl on c.CardPropertiesID = cl.CardPropertiesID where LangID = " + langid + "");
        }
        public DataTable getCreditCardByID(int CardID, int langid)
        {
            return db.exeSelect("select ca.CardID,cl.CardName,co.CompanyID,cl.BenefitContent,cl.FurtureContent,ca.CardImage,ca.CardCategoryID,ca.CardPropertiesID,cl.Description,ca.Cost,ca.Interest,cl.MoreBenefit,cl.MoreFee,cl.MoreInfo,ca.Display,co.CompanyLogo,coml.Name from Card ca join CardLang cl on ca.CardID = cl.CardID join Company co on ca.CompanyID = co.CompanyID join CompanyLang coml on co.CompanyID= coml.CompanyID where  co.Active = 1 and ca.CardID = " + CardID + " and coml.LangID=" + langid + "  and cl.LangID=" + langid + "");
        }
        public DataTable getCreditCardByIDManager(int CardID, int langid)
        {
            return db.exeSelect("select ca.CardID,cl.CardName,co.CompanyID,cl.BenefitContent,cl.FurtureContent,ca.CardImage,ca.CardCategoryID,ca.CardPropertiesID,cl.Description,ca.Cost,ca.Interest,cl.MoreBenefit,cl.MoreFee,cl.MoreInfo,ca.Display,co.CompanyLogo,coml.Name from Card ca join CardLang cl on ca.CardID = cl.CardID join Company co on ca.CompanyID = co.CompanyID join CompanyLang coml on co.CompanyID= coml.CompanyID where ca.CardID = " + CardID + " and coml.LangID=" + langid + "  and cl.LangID=" + langid + "");
        }
        public int InsertCardApplyInfo(int CustomerID, int CardID)
        {
            return db.exeUpdate("insert into CardApplyInfo values(" + CustomerID + "," + CardID + ")");
        }
        public DataTable SearchCardApplyInfo(int CustomerID, int CardID)
        {
            return db.exeSelect("Select * from CardApplyInfo where CustomerID ='" + CustomerID + "' and CardID='" + CardID + "' ");
        }
        public DataTable getCardCategory(int langid)
        {
            return db.exeSelect("Select c.CardCategoryID,cl.CardCategoryName from CardCategory c join CardCategoryLang cl on c.CardCategoryID = cl.CardCategoryID where LangID = " + langid + "");
        }
        public DataTable getCardCategoryByID(int cate, int langid)
        {
            return db.exeSelect("Select c.CardCategoryID,cl.CardCategoryName from CardCategory c join CardCategoryLang cl on c.CardCategoryID = cl.CardCategoryID where LangID = " + langid + " and c.CardCategoryID = " + cate + "");
        }
        public int InsertCardLang(CreditCard card, int langid)
        {
            return db.exeUpdate("insert into CardLang values (" + card.CardID + "," + langid + ",N'" + card.CardName + "',N'" + card.BenefitContent + "',N'" + card.FurtureContent + "',N'" + card.Description + "',N'" + card.MoreBenefit + "',N'" + card.MoreFee + "',N'" + card.MoreInfo + "')");
        }
        public int InsertCard(CreditCard card)
        {
            return db.exeUpdate("insert into Card values (N'" + card.CardName + "'," + card.CompanyID + ",N'" + card.CardImage + "'," + card.CardCategoryID + "," + card.CardPropertiesID + "," + card.Cost + "," + card.Interest + ",'" + card.Display + "')");
        }
        public int DeleteCardApply(int CardApplyID)
        {
            return db.exeUpdate("Delete from CardApply where CardApplyID = " + CardApplyID + "");
        }
        public int DeleteCard(int CardID)
        {
            return db.exeUpdate("Delete from Card where CardID = " + CardID + "");
        }
        public int DeleteCardLang(int CardID)
        {
            return db.exeUpdate("Delete from CardLang where CardID = " + CardID + "");
        }
        public int UpdateCardLang(CreditCard card, int langid)
        {
            return db.exeUpdate("update CardLang set CardName = N'" + card.CardName + "',BenefitContent=N'" + card.BenefitContent + "',FurtureContent=N'" + card.FurtureContent + "',Description=N'" + card.Description + "',MoreBenefit = N'" + card.MoreBenefit + "',MoreFee = N'" + card.MoreFee + "',MoreInfo = N'" + card.MoreInfo + "' where CardID = " + card.CardID + " and LangID =" + langid + "");
        }
        public int UpdateCard(CreditCard card)
        {
            return db.exeUpdate("update Card set CompanyID= " + card.CompanyID + ",CardCategoryID =" + card.CardCategoryID + ",CardPropertiesID=" + card.CardPropertiesID + ",CardImage=N'" + card.CardImage + "',Cost =" + card.Cost + ",Interest =" + card.Interest + ",Display = '" + card.Display + "' where CardID = " + card.CardID + "");
        }
        public int UpdateStatus(CardApplyModels card)
        {
            return db.exeUpdate("update CardApply set StatusID= " + card.StatusID + " where CardApplyID = " + card.CardApplyID + "");
        }
        public DataTable getAllCard(int langid)
        {
            return db.exeSelect("select ca.CardID,cl.CardName,co.CompanyID,cl.BenefitContent,cl.FurtureContent,ca.CardImage,ca.CardCategoryID,ca.CardPropertiesID,cl.Description,ca.Cost,ca.Interest,cl.MoreBenefit,cl.MoreFee,cl.MoreInfo,ca.Display,co.CompanyLogo,la.FlagImage  from Card ca join CardLang cl on ca.CardID = cl.CardID join Language la on cl.LangID = la.ID join Company co on ca.CompanyID = co.CompanyID where co.Active = 1 and LangID = " + langid + "");
        }
        public int CardApply(int CardID, CardApplyModels app)
        {
            return db.exeUpdate("insert into CardApply(CardID,CustomerName,CustomerEmail,CustomerPhone,CustomerIncome,CustomerDOB,StatusID) values (" + CardID + ",N'" + app.CustomerName + "',N'" + app.CustomerEmail + "',N'" + app.CustomerPhone + "',N'" + app.CustomerIncome + "',N'" + app.CustomerDOB + "'," + app.StatusID + ")");
        }


    }
}
